package com.peenongsaban.freelance.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.opensymphony.xwork2.ActionContext;
import com.peenongsaban.freelance.bean.AptitudeBean;
import com.peenongsaban.freelance.bean.AptitudeTypeBean;
import com.peenongsaban.freelance.bean.PaymentOfServiceBean;
import com.peenongsaban.freelance.bean.PictureBean;
import com.peenongsaban.freelance.bean.PostServiceBean;
import com.peenongsaban.freelance.bean.RegisterBean;
import com.peenongsaban.freelance.bean.ServiceBean;
import com.peenongsaban.freelance.connection.DatabaseConnectionPool;
import com.peenongsaban.freelance.dao.PostServiceDAO;
import com.peenongsaban.freelance.meta.AptitudeTypeMeta;
import com.peenongsaban.freelance.meta.PostServiceMeta;
import com.peenongsaban.freelance.meta.ServiceMeta;
import com.peenongsaban.freelance.model.PostServiceModel;
import com.peenongsaban.freelance.utils.DateFormatUtils;
import com.peenongsaban.freelance.utils.ExceptionUtil;
import com.peenongsaban.freelance.utils.GenerateUniqueID;

public class PostServiceDAOImpl implements PostServiceDAO{

	@Override
	public Vector<AptitudeTypeBean> getListAptitudeType() {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<AptitudeTypeBean> vector = new Vector<AptitudeTypeBean>();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT * FROM " + AptitudeTypeMeta.TABLE;
			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			databaseConnection.freeConnection(conn);
			while (rs.next()) {
				AptitudeTypeBean bean = new AptitudeTypeBean();
				bean.setAptitudeTypeID(rs.getInt(AptitudeTypeMeta.COL_APTITUDE_TYPE_ID));
				bean.setAptitudeTypeName(rs.getString(AptitudeTypeMeta.COL_APTITUDE_TYPE_NAME));
				bean.setImage_icon(rs.getString(AptitudeTypeMeta.COL_IMAGE_ICON));
				bean.setDescription(rs.getString(AptitudeTypeMeta.COL_DESCRIPTION));
				vector.add(bean);
			}
			return vector;
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);		
		} finally {
			try {
				statement.close();				
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			} finally {
				databaseConnection.destroy();
				databaseConnection = null;
				try {
					conn.close();
				} catch (SQLException e) {
					ExceptionUtil.messageException(new Throwable(), e);
				}
			}
		}
		return null;
	}

	@Override
	public Vector<PostServiceBean> getListServiceLastUpdate() {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<PostServiceBean> vector = new Vector<PostServiceBean>();
		PreparedStatement statement = null;
		try {
			String sql = "";
			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			databaseConnection.freeConnection(conn);
			while (rs.next()) {
				PostServiceBean post = new PostServiceBean();
				post.setPostID(rs.getString(PostServiceMeta.COL_POST_ID));
				post.setPostDate(rs.getDate(PostServiceMeta.COL_POST_DATE));
				
				ServiceBean service = new ServiceBean();
				service.setServiceID(rs.getString(ServiceMeta.COL_SERVICE_ID));
				service.setServiceName(rs.getString(ServiceMeta.COL_SERVICE_NAME));
				
				post.setService(service);
				
				vector.add(post);
			}
			return vector;
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);		
		} finally {
			try {
				statement.close();				
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			} finally {
				databaseConnection.destroy();
				databaseConnection = null;
				try {
					conn.close();
				} catch (SQLException e) {
					ExceptionUtil.messageException(new Throwable(), e);
				}
			}
		}
		return null;
	}

	@Override
	public boolean isAddPostService(PostServiceModel model) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement insert_service_statement = null;
		PreparedStatement insert_post_service_statement = null;
		PreparedStatement insert_payment_service_statement = null;
		PreparedStatement insert_aptitude_service_statement = null;
		PreparedStatement insert_picture_service_statement = null;
		PostServiceBean post = model.getPostService();
		
		RegisterBean register = (RegisterBean) ActionContext.getContext().getSession().get("register");
		
		if(post != null){
			try {
				conn.setAutoCommit(false);
				
				String insert_service_sql = "";
				insert_service_statement = conn.prepareStatement(insert_service_sql);
				post.getService().setServiceID(GenerateUniqueID.generateServiceID()); 
				insert_service_statement.setString(1, post.getService().getServiceID());
				insert_service_statement.setString(2, post.getService().getServiceName());
				insert_service_statement.setString(3, post.getService().getDescriptionOfService());
				insert_service_statement.setInt(4, post.getService().getDurationOfService());
				insert_service_statement.setString(5, post.getService().getFormatTime());
				insert_service_statement.setString(6, post.getService().getNoteAboutSupply());
				insert_service_statement.setTimestamp(7, DateFormatUtils.getTimeStamp());
				insert_service_statement.executeUpdate();
				
				String insert_post_service_sql = "";
				insert_post_service_statement = conn.prepareStatement(insert_post_service_sql);
				insert_post_service_statement.setString(1, post.getService().getServiceID());
				insert_post_service_statement.setString(2, "new");
				insert_post_service_statement.setDate(3, DateFormatUtils.getDate());
				insert_post_service_statement.setString(4, post.getService().getServiceID());
				insert_post_service_statement.setString(5, register.getUser().getIdCard());
				insert_post_service_statement.setTimestamp(6, DateFormatUtils.getTimeStamp());
				insert_post_service_statement.executeUpdate();
				
				for (AptitudeBean aptitude : post.getListAptitude()) {
					String insert_aptitude_service_sql = "";
					insert_aptitude_service_statement = conn.prepareStatement(insert_aptitude_service_sql);
					insert_aptitude_service_statement.setString(1, post.getService().getServiceID());
					insert_aptitude_service_statement.setInt(2, aptitude.getAptitudeID());
					insert_aptitude_service_statement.setTimestamp(3, DateFormatUtils.getTimeStamp());
					insert_aptitude_service_statement.executeUpdate();
				}
				
				for (PaymentOfServiceBean payment : post.getService().getListPaymentOfService()) {
					String insert_payment_service_sql = "";
					payment.setPaymentId(GenerateUniqueID.generatePaymentID());
					insert_payment_service_statement = conn.prepareStatement(insert_payment_service_sql);
					insert_payment_service_statement.setString(1, payment.getPaymentId());
					insert_payment_service_statement.setString(2, post.getService().getServiceID());
					insert_payment_service_statement.setInt(3, payment.getPaymentType().getPaymentTypeID());
					insert_payment_service_statement.setDouble(4, payment.getPrice());
					insert_payment_service_statement.setString(5, payment.getDescription());
					insert_payment_service_statement.setTimestamp(6, DateFormatUtils.getTimeStamp());
					insert_payment_service_statement.executeUpdate();
					
					for (PictureBean picture : payment.getListPicture()) {
						String insert_picture_service_sql = "";
						picture.setPictureID(GenerateUniqueID.generatePictureID());
						insert_picture_service_statement = conn.prepareStatement(insert_picture_service_sql);
						insert_picture_service_statement.setString(1, picture.getPictureID());
						insert_picture_service_statement.setString(2, picture.getPictureLocation());
						insert_picture_service_statement.setString(3, picture.getDescription());
						insert_picture_service_statement.setTimestamp(4, DateFormatUtils.getTimeStamp());
						insert_picture_service_statement.executeUpdate();
					}
				}
				
				databaseConnection.freeConnection(conn);
				
				conn.commit();
				
				return true;
			} catch (SQLException e) {
				ExceptionUtil.messageException(new Throwable(), e);
				try {
					conn.rollback();
				} catch (SQLException e1) {
					ExceptionUtil.messageException(new Throwable(), e1);
				}			
			} finally {
				try {
					if(insert_service_statement != null)
						insert_service_statement.close();
					if(insert_post_service_statement != null)
						insert_post_service_statement.close();
					if(insert_payment_service_statement != null)
						insert_payment_service_statement.close();
					if(insert_picture_service_statement != null)
						insert_picture_service_statement.close();
					if(insert_aptitude_service_statement != null)
						insert_aptitude_service_statement.close(); 
					
					databaseConnection.destroy();
					databaseConnection = null;
					conn.close();
				} catch (Exception ex) {
					ExceptionUtil.messageException(new Throwable(), ex);
				}
			}
		}
		return false;
	}	
}
